package com.example.dao.impl;

import com.example.dao.IdCollectionDao;
import com.example.model.IdCollection;
import com.example.util.DBUtil;
import com.example.vo.IdCollectionVO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class IdCollectionDaoImpl implements IdCollectionDao {
    @Override
    public List<IdCollectionVO> queryAll(String username) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<IdCollectionVO> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();

            String sql =  "select ic.cname as cname,ic.cid as cid" +
                    ",c.releasenum as releasenum" +
                    ",ic.price as price,c.cimg as cimg" +
                    " from tb_idcollection ic left join tb_collection c " +
                    "on ic.cid = c.id";
            if(username != null && !("".equals(username))){
                     sql += " and ic.username = '"+username+"'";
            }
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                IdCollectionVO vo = new IdCollectionVO();
                vo.setCid(rs.getInt("cid"));
                vo.setCname(rs.getString("cname"));
                vo.setReleaseNum(rs.getInt("releasenum"));
                vo.setPrice(rs.getDouble("price"));
                vo.setCimg(rs.getString("cimg"));
                list.add(vo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(connection,ps,rs);
        }
        return list;
    }
}
